package com.java;

import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class PreparedStatementInsertTest {

    private Connection connection;
    private Statement statement;
    @Test
    public void TestStatement() throws Exception {
        // 增加
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户的ID：");
        String id = scanner.nextLine();
        System.out.println("请输入用户名：");
        String name = scanner.nextLine();
        System.out.println("请输入用户的工作：");
        String work = scanner.nextLine();
        String sql1 = "insert into status(id,name,work)values" +
                "("+id+",'"+name+"','"+work+"')";

        int value = InsertTest(sql1);
        if (value > 0){
            System.out.println("成功影响的列值数："+value);
        }else {
            System.out.println("插入失败！");
        }

        // 删除(id值自行设置)
        String sql2 = "delete from status where id="+"10";
        int delete = DeleteTest(sql2);
        if (delete > 0){
            System.out.println("成功影响的列值数："+delete);
        }else {
            System.out.println("删除失败！");
        }

        // 修改
        String sql3 = "update status set name = "+"'abcd'"+"where id = "+"9";
        int update = UpdateTest(sql3);
        if (update > 0){
            System.out.println("成功影响的列值数："+update);
        }else {
            System.out.println("更新失败！");
        }

        // 查询
        String sql4 = "select * from status";
        ResultSet resultSet = SelectTest(sql4);
        while (resultSet.next()){
            int returnID = resultSet.getInt(1);
            String returnName = resultSet.getString(2);
            String returnWork = resultSet.getString(3);
            System.out.println(new StatmentDataTest(returnID,returnName,
                    returnWork).toString());
        }
    }

    public int InsertTest(String sql) {
        try {
            //获取连接
            connection = JDBCUtilTools.getConnection();
            statement = connection.createStatement();
            //执行SQL语句并返回结果
            return statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            JDBCUtilTools.CloseConnection(connection,statement);
        }
    }

    public int DeleteTest(String sql) throws Exception {
        try {
            //获取连接
            connection = JDBCUtilTools.getConnection();
            statement = connection.createStatement();
            //执行SQL语句并返回结果
            return statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            JDBCUtilTools.CloseConnection(connection,statement);
        }
    }

    public int UpdateTest(String sql) throws Exception {
        try {
            //获取连接
            connection = JDBCUtilTools.getConnection();
            statement = connection.createStatement();
            //执行SQL语句并返回结果
            return statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            JDBCUtilTools.CloseConnection(connection,statement);
        }
    }

    public ResultSet SelectTest(String sql) throws Exception {
        //获取连接
        connection = JDBCUtilTools.getConnection();
        statement = connection.createStatement();
        //执行SQL语句并返回结果
        return statement.executeQuery(sql);
    }
}
